package com.ceba.test.modules.usermanage.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import abc.data.*;
import com.ceba.test.base.utils.StringUtil;
import org.json.JSONObject;

import com.ceba.test.base.configs.CebaConfig;
import com.ceba.base.web.dao.IBaseDao;
import com.ceba.test.modules.usermanage.entity.UUser;
import com.ceba.test.modules.usermanage.entity.Udepartment;
import com.ceba.test.modules.usermanage.entity.Uprivilege;

import abc.data.dataSource.SqlDataSource;
import abc.security.Encoder;

public class UserDao   extends  IBaseDao {

	
	public String getUserRoleByUserId(String userId){
		Sql sql = new Sql("SELECT User_ID,Object_Id FROM u_privilege WHERE User_ID=?");
		sql.addParameter(userId);
		DataTable dt = SqlSessionManager.getDataTable(sql);
		if(dt !=null && dt.getRowCount()>0){
			return dt.getRow(0).getString("Object_Id");
		}
		return "";
	}
	
	public DataTable getOrganizeUserList (Map<String,Object> dic) throws Exception{
		SqlDataSource dataSource = new SqlDataSource();
		String tj="";
		//如果是签名版（2）,只有2个角色;
		if(CebaConfig.USE_VERSION.equals("2")){
			tj=" AND u_role.ID IN ('100','109','107')";
		}
		
		String orderBy = "u_user.UPDATE_TIME DESC";
		if (dic.containsKey("sort[0][field]")) {
			
			orderBy = Encoder.encodeForSQL( (String)dic.get("sort[0][field]")+" "+(String)dic.get("sort[0][dir]"));
		}
		String key = (String)dic.get("key");
		Sql sql = new Sql(
				"SELECT u_user.ID,u_user.NAME AS NAME,LOGIN_ID AS LOGIN_ID,u_role.NAME AS ROLE_NAME,"
				+ " DEPARTMENT_ID AS DEPARTMENT_ID,u_user.REMARK AS REMARK,u_role.ID AS ROLE_ID "
				+ " FROM u_user "
				+ " LEFT OUTER JOIN u_privilege ON u_privilege.USER_ID=u_user.ID "
				+ " LEFT OUTER JOIN u_role ON u_role.ID=u_privilege.OBJECT_ID AND u_privilege.FLAG=1 "
				+ " where department_ID=?"
				+ " AND (u_user.NAME like '%"+key+"%' OR u_role.NAME like '%"+key+"%' OR LOGIN_ID like '%"+key+"%' OR u_user.REMARK like '%"+key+"%') "
				+ tj
				+ "  ORDER BY " + orderBy);
		
		sql.addParameter("PID", (String)dic.get("PID"));
		dataSource.getSelectCommands().add(sql);
		DataTable dt = dataSource.query(0, dic);
		return dt;
	}

	/**
	 * 分页
	 * @param
	 * @return
	 * @throws Exception
	 */
	public DataTable getUserList (Map<String,Object> dic,String orderBy) throws Exception{
		String key = (String)dic.get("key");
		String tj="";
		if(CebaConfig.USE_VERSION.equals("3")){
			tj=" AND u_role.ID IN ('109','200')";
		}
		
		if (StringUtil.isEmptyString(orderBy)) {
			orderBy = "u_user.CREATE_TIME DESC";
		}
		if(key == null){
			key="";
		}
		Sql sql = new Sql(
					"SELECT u_user.ID,u_user.NAME AS NAME,LOGIN_ID AS LOGIN_ID,u_role.NAME AS ROLE_NAME,"
							+ " DEPARTMENT_ID AS DEPARTMENT_ID,u_user.REMARK AS REMARK,u_role.ID AS ROLE_ID "
							+ " FROM u_user "
							+ " LEFT OUTER JOIN u_privilege ON u_privilege.USER_ID=u_user.ID "
							+ " LEFT OUTER JOIN u_role ON u_role.ID=u_privilege.OBJECT_ID AND u_privilege.FLAG=1 "
							+ " where u_user.ID != 'verAdmin' AND u_user.ID != '999999999999999999999999999999'"
							+ " AND (u_user.NAME like '%"+key+"%' OR u_role.NAME like '%"+key+"%' OR LOGIN_ID like '%"+key+"%' OR u_user.REMARK like '%"+key+"%') "
							+ tj
							+ "  ORDER BY " + orderBy);

        SqlDataSource dataSource = new SqlDataSource();
		dataSource.getSelectCommands().add(sql);
		DataTable dt = dataSource.query(0, dic);
		return dt;
	}

	public long queryUserCount(){
		String tj="";
		if(CebaConfig.USE_VERSION.equals("3")){
			tj=" AND u_role.ID IN ('109','200')";
		}
		Sql sql = new Sql(
				"SELECT count(*)"
						+ " FROM u_user "
						+ " LEFT OUTER JOIN u_privilege ON u_privilege.USER_ID=u_user.ID "
						+ " LEFT OUTER JOIN u_role ON u_role.ID=u_privilege.OBJECT_ID AND u_privilege.FLAG=1 "
						+ " where u_user.ID != 'verAdmin' AND u_user.ID != '999999999999999999999999999999'"
						+ tj);
		long count = 0;
		DataTable dataTable  = SqlSessionManager.getDataTable(sql);
		if(dataTable!=null && dataTable.getRowCount()>0){
			DataRow dataRow = dataTable.getRow(0);
			count = dataRow.getLong("count(*)");
		}
		return count;
	}

	/**
	 * 不分页
	 * @param
	 * @return
	 * @throws Exception
	 */
	public DataTable getUserListBySou (String key,String orderBy) throws Exception{
		SqlDataSource dataSource = new SqlDataSource();
		String tj="";
		//如果是签名版（2）,只有2个角色;
		if(CebaConfig.USE_VERSION.equals("3")){
			tj=" AND u_role.ID IN ('109','200')";
		}

		if(orderBy==null||orderBy.equals("")){
			orderBy = "u_user.CREATE_TIME DESC";
		}
		/*if (dic.containsKey("sort[0][field]")) {

			orderBy = Encoder.encodeForSQL( (String)dic.get("sort[0][field]")+" "+(String)dic.get("sort[0][dir]"));
		}*/
		if(key == null){
			key="";
		}
		Sql sql = new Sql(
					"SELECT u_user.ID,u_user.NAME AS NAME,LOGIN_ID AS LOGIN_ID,u_role.NAME AS ROLE_NAME,"
							+ " DEPARTMENT_ID AS DEPARTMENT_ID,u_user.REMARK AS REMARK,u_role.ID AS ROLE_ID "
							+ " FROM u_user "
							+ " LEFT OUTER JOIN u_privilege ON u_privilege.USER_ID=u_user.ID "
							+ " LEFT OUTER JOIN u_role ON u_role.ID=u_privilege.OBJECT_ID AND u_privilege.FLAG=1 "
							+ " where u_user.ID != 'verAdmin' AND u_user.ID != '999999999999999999999999999999'"
							+ " AND (u_user.NAME like '%"+key+"%' OR u_role.NAME like '%"+key+"%' OR LOGIN_ID like '%"+key+"%' OR u_user.REMARK like '%"+key+"%') "
							+ tj
							+ "  ORDER BY " + orderBy);
		return SqlSessionManager.getDataTable(sql);
	}

	public String getUser(Map<String,Object> dic) throws Exception{
		
		SqlDataSource dataSource = new SqlDataSource();
		
		String tj="";
		//如果是签名版（2）,只有2个角色;
		if(CebaConfig.USE_VERSION.equals("2")){
			tj=" AND u_role.ID IN ('100','109','107')";
		}
		
		String orderBy = "u_user.UPDATE_TIME DESC";
	/*	if (dic.containsKey("sort[0][field]")) {
			
			orderBy = Encoder.encodeForSQL( (String)dic.get("sort[0][field]")+" "+(String)dic.get("sort[0][dir]"));
		}*/
		String key = (String)dic.get("key");
		Sql sql = new Sql(
				"SELECT u_user.ID,u_user.NAME AS NAME,LOGIN_ID AS LOGIN_ID,"
				+ " u_department.ID AS DEPARTMENT_ID,u_user.Remark AS REMARK,u_role.ID AS ROLE_ID "
				+ " FROM u_user "
				+ " LEFT OUTER JOIN u_privilege ON u_privilege.USER_ID=u_user.ID "
				+ " LEFT OUTER JOIN u_role ON u_role.ID=u_privilege.OBJECT_ID AND u_privilege.FLAG=1 "
				+ " LEFT OUTER JOIN u_department ON u_user.department_ID=u_department.ID"
				+ " where u_user.ID=?"
				+ " AND (u_user.NAME like '%"+key+"%' OR u_role.NAME like '%"+key+"%' OR LOGIN_ID like '%"+key+"%' OR u_user.REMARK like '%"+key+"%') "
				+ tj
				+ "  ORDER BY " + orderBy);
		
		sql.addParameter("ID", (String)dic.get("ID"));

		dataSource.getSelectCommands().add(sql);

		DataTable dt = dataSource.query(0, dic);
		JSONObject json = new JSONObject();
		json.put("get",dt.toJSON());
		return json.toString();
	}
	
	
	public DataTable editOrganizeList(Map<String,Object> dic) throws Exception{
		SqlDataSource dataSource = new SqlDataSource();
		String orderBy = "u_department.UPDATE_TIME DESC";
		if (dic.containsKey("sort[0][field]")) {
			
			orderBy = Encoder.encodeForSQL( (String)dic.get("sort[0][field]")+" "+(String)dic.get("sort[0][dir]"));
		}
		Sql sql = new Sql(
				"SELECT ID,ID AS DEPARTMENT_ID,FULL_NAME AS FULL_NAME FROM u_department "
				+ "  ORDER BY " + orderBy);

		dataSource.getSelectCommands().add(sql);
		DataTable dt = dataSource.query(0, dic);
		return dt;
		
	}
	
	
	public  DataTable getOrganizeList(Map<String,Object> dic) throws Exception{
		SqlDataSource dataSource = new SqlDataSource();
		String orderBy = "u_department.UPDATE_TIME DESC";

	/*	if (dic.containsKey("sort[0][field]")) {
			
			orderBy = Encoder.encodeForSQL( (String)dic.get("sort[0][field]")+" "+(String)dic.get("sort[0][dir]"));
		}*/
		String key = (String)dic.get("key");
		Sql sql = new Sql(
				"SELECT ID,NAME AS NAME,FULL_NAME AS FULL_NAME,REMARK AS REMARK FROM u_department "
				+ " WHERE PID=?"
				+ " AND (NAME like '%"+key+"%' OR FULL_NAME like '%"+key+"%' OR REMARK like '%"+key+"%') "
				+ "  ORDER BY " + orderBy);
		
		sql.addParameter("PID", (String)dic.get("PID"));
		dataSource.getSelectCommands().add(sql);
		DataTable dt = dataSource.query(0, dic);
		return dt;
	}
	
	
	
	@SuppressWarnings("unused")
	public  DataTable editRole(Map<String,Object> dic) throws Exception{
		String tj="";
		//如果是签名版（2）,只有2个角色;
		if(CebaConfig.USE_VERSION.equals("2")){
			tj=" AND ID IN ('100','109','107')";
		}
		else if(CebaConfig.USE_VERSION.equals("3")){
			tj=" AND ID IN ('109','200')";
		} 
		Sql sql = new Sql("SELECT ID AS ROLE_ID,NAME AS ROLE_NAME  FROM u_role where Row_State=1 "+tj+"");				
		DataTable dt = SqlSessionManager.getDataTable(sql);
		
		return dt;
	}
	
	
	/**
	 * 获取角色信息
	 * @param
	 * @return
	 */
	public  DataTable getRole() throws Exception{
		
		String tj="";
		//如果是签名版（2）,只有2个角色;
		if(CebaConfig.USE_VERSION.equals("2")){
			tj=" AND ID IN ('100','109','107')";
		}
		else if(CebaConfig.USE_VERSION.equals("3")){
			tj=" AND ID IN ('109','200')";
		} 
		Sql sql = new Sql("SELECT ID,NAME AS NAMEN  FROM u_role where Row_State=1 "+tj+"");
		DataTable dt = SqlSessionManager.getDataTable(sql);
		
		return dt;
		
	}
	
	
	
	
	/**
	 * 添加组织
	 * @return
	 */
	public  boolean  addOrganize(Udepartment udepartment) throws Exception{
		Sql sql = new Sql("insert into u_department (ID,Name,Full_Name,Remark,PID,Step) VALUES(?,?,?,?,?,?)");
	    sql.addParameter(udepartment.getId());
	    sql.addParameter(udepartment.getName());
	    sql.addParameter(udepartment.getFullname());
	    sql.addParameter(udepartment.getRemapk());
	    sql.addParameter(udepartment.getPid());
	    sql.addParameter(udepartment.getStemp());
	    
	   boolean sucess=  SqlSessionManager.execute(sql);
	   
	   return sucess;
	    
	}
	
	
    public long maxSetp(String pid) throws Exception {
    	long c=SqlSessionManager.getFieldLongValue("SELECT MAX(Step) FROM u_department WHERE PID='"+pid+"'");
       return  c;
	   
      }
	
    
    
    /**
     * 添加用户
     * 
     */
    
     public boolean addUser(UUser user) throws Exception{
    	Sql sql = new Sql("insert into u_user (ID,NAME,LOGIN_ID,DEPARTMENT_ID,PASSWORD,Remark,Create_Time,Create_User) VALUES(?,?,?,?,?,?,?,?)"); 
    	sql.addParameter(user.getId());
 	    sql.addParameter(user.getName());
 	    sql.addParameter(user.getLoginId());
 	    sql.addParameter(user.getDepartmentId());
 	    sql.addParameter(user.getPassword());
 	    sql.addParameter(user.getRemark());
 	    sql.addParameter(user.getCreateTime());
 	    sql.addParameter(user.getCreateUser());
 	   boolean sucess=  SqlSessionManager.execute(sql);
 	   return sucess;
     }
     
     
     /**
      * 修改用户 
      */
     
     public boolean updateUser (UUser user) throws Exception {
    		Sql sql = new Sql("update u_user set NAME=?,LOGIN_ID=?,DEPARTMENT_ID=?,PASSWORD=?,Remark=?,Create_Time=?,Create_User=? WHERE ID=?"); 
     	    sql.addParameter(user.getName());
     	    sql.addParameter(user.getLoginId());
     	    sql.addParameter(user.getDepartmentId());
     	    sql.addParameter(user.getPassword());
     	    sql.addParameter(user.getRemark());
     	    sql.addParameter(user.getCreateTime());
     	    sql.addParameter(user.getCreateUser());
     	    sql.addParameter(user.getId());
     	   boolean sucess=  SqlSessionManager.execute(sql);
     	   return sucess;
     }
     
     
     
     
     
     public boolean  addUprivilege (Uprivilege uprivilege) throws Exception{
    		Sql sql = new Sql("insert into u_privilege (ID,USER_ID,OBJECT_ID,FLAG) VALUES(?,?,?,?)"); 
    		sql.addParameter(uprivilege.getId());
    		sql.addParameter(uprivilege.getUserId());
    		if(uprivilege.getObjectId()!=null && !uprivilege.getObjectId().equals("")){
				sql.addParameter(uprivilege.getObjectId());
			}else{
    			return false;
			}
    		sql.addParameter(uprivilege.getFlag());
		    boolean sucess=  SqlSessionManager.execute(sql);
     	    return sucess;
     }
     
    
     /**
      * 
      *修改用户权限 
      * 
      */
     public boolean updateUprivilege(String role_id,String id) throws Exception {
         	Sql sql=new Sql("update u_privilege set OBJECT_ID=? WHERE USER_ID=? ");
			sql.addParameter(role_id);
			sql.addParameter(id);
		    boolean suc =SqlSessionManager.execute(sql);
		   return suc;
     }
    
    
     
    /**
     * 删除用户
     */
     
     public boolean deleteUser (String id) throws Exception{
       SqlDataSource dataSource = new SqlDataSource();
       Sql sql = new Sql("DELETE FROM u_user WHERE ID=?");
		sql.addParameter("ID",id);
		dataSource.getDeleteCommands().add(sql);
	  boolean result0 = dataSource.delete();
	  return result0;
    	 
     }
    
     
     public boolean deleteUprivilege(String id) throws Exception{
    	    SqlDataSource dataSource = new SqlDataSource();
    		Sql sql1 = new Sql("DELETE FROM u_privilege WHERE User_ID=?");
			sql1.addParameter("User_ID",id);
			dataSource.getDeleteCommands().add(sql1);
		    boolean result1 = dataSource.delete();
		    return result1;
     }

	public boolean getUserByLoginId(String loginUserId){
		Sql sql = new Sql("select * from u_user where LOGIN_ID ='"+loginUserId+"'");
		DataTable dt = SqlSessionManager.getDataTable(sql);
		if(dt!=null&&dt.getRowCount()>0){
			return true;
		}else{
			return false;
		}
	}
     
     public boolean getUserQueryByName(String loginId){
 		 Sql sql = new Sql("SELECT ID,NAME AS NAME FROM u_user WHERE Login_ID='"+loginId+"'");
 		 long count = SqlSessionManager.getCount(sql);
 		 boolean flag = true;
 		 if(count > 0){
 			flag = false;
 		 }
    	 return  flag;
     }

     public boolean getUserQueryByNameAndId(String loginId,String userId){
 		 Sql sql = new Sql("SELECT ID,NAME AS NAME FROM u_user WHERE Login_ID='"+loginId+"' and ID <> '"+userId+"'");
 		 long count = SqlSessionManager.getCount(sql);
 		 boolean flag = true;
 		 if(count > 0){
 			flag = false;
 		 }
    	 return  flag;
     }

 	public  String getUserRole(String userId ) throws Exception{
		return  getUserRoleByUserId(userId);
	}
 	
 	/**
	 * 获取所有的用户
	 * @return
	 * @throws Exception
	 */
	public List<UUser> getUserIds() throws Exception{
		List<UUser> userList = new ArrayList<UUser>();
		Sql sql = new Sql("SELECT u_user.ID,CONCAT(u_role.`Name`,':',u_user.`Name`) AS NAME"
				 +" FROM u_user"
				 +" LEFT OUTER JOIN u_privilege ON u_privilege.User_ID=u_user.ID"
				 +" LEFT OUTER JOIN u_role ON u_privilege.Object_Id=u_role.ID"
				 );
		DataTable dt = SqlSessionManager.getDataTable(sql);
		if(dt!=null && dt.getRowCount()>0){
			for (DataRow row : dt.getRows()) {
				UUser user = new UUser();
				user.setId(row.getString("ID"));
				user.setName(row.getString("NAME"));
				userList.add(user);
			}
		}
		return userList;
	}
	
	/**
	 * 获取未添加用户章的用户
	 * @return
	 * @throws Exception
	 */
	public List<UUser> getUserIdsNotInStamp() throws Exception{
		List<UUser> userList = new ArrayList<UUser>();
		Sql sql = new Sql("SELECT u_user.ID,CONCAT(u_role.`Name`,':',u_user.`Name`) AS NAME"
				 +" FROM u_user"
				 +" LEFT OUTER JOIN u_privilege ON u_privilege.User_ID=u_user.ID"
				 +" LEFT OUTER JOIN u_role ON u_privilege.Object_Id=u_role.ID"
				 +" WHERE u_user.ID NOT IN (SELECT USER_ID FROM g_stamp WHERE USER_ID IS NOT NULL)");
		DataTable dt = SqlSessionManager.getDataTable(sql);
		if(dt!=null && dt.getRowCount()>0){
			for (DataRow row : dt.getRows()) {
				UUser user = new UUser();
				user.setId(row.getString("ID"));
				user.setName(row.getString("NAME"));
				userList.add(user);
			}
		}
		return userList;
	}

	/**
	 * 根据用户id查询用户信息
	 * @param userId
	 * @return
	 */
	public Map<String,Object> queryUserById(String userId){
		Sql sql = new Sql("select u.ID,u.NAME,u.LOGIN_ID,ur.NAME AS ROLE_NAME,ur.ID AS ROLE_ID,u.Remark" +
				" FROM u_user u LEFT JOIN u_privilege up ON u.ID = up.User_ID LEFT JOIN u_role ur ON up.Object_Id = ur.ID"+
				" where u.ID = '"+userId+"'");
		DataTable dt = SqlSessionManager.getDataTable(sql);
		Map<String,Object> map = new HashMap<>();
		if(dt!=null && dt.getRowCount()>0){
			DataRow dataRow = dt.getRow(0);
			map.put("ID",dataRow.getString("ID"));
			map.put("NAME",dataRow.getString("NAME"));
			map.put("LOGIN_ID",dataRow.getString("LOGIN_ID"));
			map.put("ROLE_NAME",dataRow.getString("ROLE_NAME"));
			map.put("ROLE_ID",dataRow.getString("ROLE_ID"));
			map.put("Remark",dataRow.getString("Remark"));
		}
		return map;
	}
	
	/**
	 * 根据ID获取用户
	 */
	public DataTable getUserById(String userId){
		Sql sql = new Sql("select ID,Name FROM u_user where ID='"+userId+"'");
		DataTable dt = SqlSessionManager.getDataTable(sql);
		if(dt!=null && dt.getRowCount()>0){
			return dt;
		}
		return null;
	}
}
